﻿using System;
using System.Collections.Generic;
using System.Linq;
using SlamCms.Common;

namespace SlamCms.Data
{
	internal static class TagTreeDataHelpers
	{
		public static IEnumerable<TagHierarchyPosition> GetTagHierarchy(this Database database)
		{
			var sql = @"select PositionId, ParentPositionId, TagId, TagCategoryId, SortOrder, TagTreeId
						from TagHierarchyPosition 
						order by TagTreeId, SortOrder";
			return database.Query<TagHierarchyPosition>(sql);
		}

        [Obsolete]
		public static IDictionary<int, int> RetrieveTagTreeContentItemCount(this Database database, string tagTreeId, string[] zoneIds, string[] contentTypeIds, string[] taggedWithAny = null)
		{
			var taggedWithAnyWhere = string.Empty;
			if (taggedWithAny != null && taggedWithAny.Length > 0)
			{
				var taggedWithAnySerialialized = taggedWithAny.ToSqlIn();
				taggedWithAnyWhere = @"and exists (select 1 
										from ContentItemTag cit2 with (nolock)
										inner join Tag t with (nolock) on cit2.TagID = t.TagID
										where ContentItemID = ci.ContentItemID 
										and t.Identifier in ({0}))".F(taggedWithAnySerialialized);
			}

			var sql = @"select th.PositionID,
							   count(*) ContentItemCount
						from TagHierarchyPosition th with (nolock)
						inner join ContentItemTag cit with (nolock) on cit.TagID = th.TagID
						inner join ContentItem ci with (nolock) on ci.ContentItemId = cit.ContentItemId
						inner join [Slam].List li with (nolock) on li.ListId = ci.ListId
						inner join (select SiteID from ZoneSite with (nolock) where ZoneID in ({2})) s on s.SiteID = li.SiteID
						where th.TagTreeID = '{0}'
						and th.ParentPositionID is null
						and ci.ContentTypeId in ({1})
						{3}
						group by th.PositionID
						having count(*) > 0".F(tagTreeId, contentTypeIds.ToSqlIn(), zoneIds.ToSqlIn(), taggedWithAnyWhere);
	
			return database.Query<TagHierarchyPositionCount>(sql).ToDictionary(x => x.PositionId, x => x.ContentItemCount);
		}
	}
}
